package com.summary.richtext.utils;

import com.google.common.collect.Lists;
import com.summary.richtext.annotation.RichExcelField;
import com.summary.richtext.utils.entity.ImportFontStyle;
import com.summary.richtext.utils.enums.RichFontStyle;
import com.summary.richtext.utils.richExcelUtil.PoiStyleUtil;
import com.summary.richtext.utils.richExcelUtil.Reflections;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
public class ImportRichExcel implements Serializable {

    /**
     * 工作薄对象
     */
    private final Workbook wb;

    /**
     * 公式转换工具(基本用于判断 Workbook 属于 HSSFWorkbook 还是 XSSFWorkbook)
     */
    private HSSFFormulaEvaluator hssEvaluator;

    private XSSFFormulaEvaluator xssEvaluator;

    /**
     * 工作表对象
     */
    private final Sheet sheet;

    /**
     * 距离数据最近的标题索引（行号-1）
     * 如：Excel第1行为标题，第2行为标题，
     * 则headerIndex为1，数据起始索引为headerIndex+1
     */
    private final int headerIndex;

    /**
     * 构造函数
     *
     * @param file        导入文件对象
     * @param headerIndex 距离数据最近的标题索引（行号-1）
     *                    如：Excel第1行为标题，第2行为标题，
     *                    则headerIndex为1，数据起始索引为headerIndex+1
     * @param sheetIndex  工作表编号（sheet页索引）
     * @throws IOException IOException
     */
    public ImportRichExcel(File file, int headerIndex, int sheetIndex) throws IOException {
        this(file.getName(), new FileInputStream(file), headerIndex, sheetIndex);
    }

    /**
     * 构造函数
     *
     * @param multipartFile 导入文件对象
     * @param headerIndex   距离数据最近的标题索引（行号-1）
     *                      如：Excel第1行为标题，第2行为标题，
     *                      则headerIndex为1，数据起始索引为headerIndex+1
     * @param sheetIndex    工作表编号（sheet页索引）
     * @throws IOException IOException
     */
    public ImportRichExcel(MultipartFile multipartFile, int headerIndex, int sheetIndex) throws IOException {
        this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerIndex, sheetIndex);
    }

    /**
     * 构造函数
     *
     * @param fileName    导入文件对象
     * @param headerIndex 距离数据最近的标题索引（行号-1）
     *                    如：Excel第1行为标题，第2行为标题，
     *                    则headerIndex为1，数据起始索引为headerIndex+1
     * @param sheetIndex  工作表编号（sheet页索引）
     * @throws IOException IOException
     */
    public ImportRichExcel(String fileName, InputStream is, int headerIndex, int sheetIndex) throws IOException {
        if (StringUtils.isBlank(fileName)) {
            throw new RuntimeException("导入文档为空!");
        } else if (fileName.toLowerCase().endsWith("xls")) {
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
            this.wb = hssfWorkbook;
            hssEvaluator = new HSSFFormulaEvaluator(hssfWorkbook);
        } else if (fileName.toLowerCase().endsWith("xlsx")) {
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
            this.wb = xssfWorkbook;
            xssEvaluator = new XSSFFormulaEvaluator(xssfWorkbook);
        } else {
            throw new RuntimeException("文档格式不正确!");
        }
        if (this.wb.getNumberOfSheets() < sheetIndex) {
            throw new RuntimeException("文档中没有工作表!");
        }
        this.sheet = this.wb.getSheetAt(sheetIndex);
        this.headerIndex = headerIndex;
    }

    /**
     * 获取导入数据列表(不处理合并单元格的数据,合并的单元格数据为空)
     *
     * @param cls    导入对象类型
     */
    public <E> List<E> getDataList(Class<E> cls) throws Exception {
        return this.getDataList(cls, false);
    }

    /**
     * 获取导入数据列表(自动处理合并单元格的数据,合并的单元格数据为合并目标单元格的数据)
     *
     * @param cls               导入对象类型
     * @param isAutoHandleMerge 是否自动处理合并单元格的数据
     */
    public <E> List<E> getDataList(Class<E> cls, boolean isAutoHandleMerge) throws Exception {
        //=========================收集注解数据集=======================
        List<Object[]> annotationList = Lists.newArrayList();//Object[0]: @RichExcelField    Object[1]: Field/method
        Field[] fs = cls.getDeclaredFields();
        for (Field field : fs) {
            RichExcelField richExcelField = field.getAnnotation(RichExcelField.class);
            if (richExcelField != null && (richExcelField.type() == 0 || richExcelField.type() == 2)) {
                annotationList.add(new Object[]{richExcelField, field});
            }
        }
        annotationList.sort(Comparator.comparingInt(o -> ((RichExcelField) o[0]).sort()));

        //======================收集导入数据集==========================
        List<E> dataList = Lists.newArrayList();

        for (int i = headerIndex + 1; i <= this.sheet.getLastRowNum(); i++) {//遍历数据行
            E e = cls.newInstance();//new 一个反射实例对象
            int column = 0;
            Row row = this.sheet.getRow(i);

            for (Object[] os : annotationList) {//遍历实体注解及字段集合
                Object val;
                if (isAutoHandleMerge && this.isMergedRegion(i, column)) {//合并单元格处理
                    val = this.getMergedRegionValue(i, column);
                } else {//非合并单元格处理
                    val = this.getCellValue(row, column, (RichExcelField)os[0]);
                }
                column++;
                if (!StringUtils.isBlank(val.toString())) {
                    RichExcelField richExcelField = (RichExcelField) os[0];
                    Class<?> valType = ((Field) os[1]).getType();
                    try {
                        boolean typeInfer = richExcelField.typeInfer();
                        if (typeInfer) {
                            try {
                                val = DateUtil.getJavaDate((Double) val);
                                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                                val = simpleDateFormat.format(val);
                            } catch (Exception a) {
                                String s = String.valueOf(val.toString());
                                if (StringUtils.endsWith(s, ".0")) {
                                    val = StringUtils.substringBefore(s, ".0");
                                } else {
                                    val = String.valueOf(val.toString());
                                }
                            }
                        } else {
                            if (valType == String.class) {
                                String s = String.valueOf(val.toString());
                                if (StringUtils.endsWith(s, ".0")) {
                                    val = StringUtils.substringBefore(s, ".0");
                                } else {
                                    val = String.valueOf(val.toString());
                                }
                            } else if (valType == Integer.class) {
                                val = Double.valueOf(val.toString()).intValue();
                            } else if (valType == Long.class) {
                                val = Double.valueOf(val.toString()).longValue();
                            } else if (valType == Double.class) {
                                val = Double.valueOf(val.toString());
                            } else if (valType == Float.class) {
                                val = Float.valueOf(val.toString());
                            } else if (valType == Date.class) {
                                val = DateUtil.getJavaDate((Double) val);
                            } else {
                                if (richExcelField.fieldType() != Class.class) {
                                    val = richExcelField.fieldType().getMethod("getValue", String.class).invoke(null, val.toString());
                                } else {
                                    val = Class.forName(this.getClass().getName().replaceAll(this.getClass().getSimpleName(),
                                            "fieldtype." + valType.getSimpleName() + "Type")).getMethod("getValue", String.class).invoke(null, val.toString());
                                }
                            }
                        }
                    } catch (Exception ex) {
                        if (!StringUtils.isBlank(val.toString())) {
                            throw new Exception("数据错误发生位置 第" + (i + 1) + "行,第" + numberToLetter(column) + "列 错误原因: 出现格式错误请更改！");
                        } else {
                            log.info("Get cell value [" + (i + 1) + "," + column + "] error: " + ex.toString());
                        }
                        val = null;
                    }
                    //set值到实体对象e
                    Reflections.invokeSetter(e, ((Field) os[1]).getName(), val);
                }
            }
            dataList.add(e);
        }
        return dataList;
    }

    /**
     * 获取单元格值
     *
     * @param row    获取的行
     * @param column 获取单元格列号
     * @param ef     单元格对应注解字段信息（用于判断单元格内容是否需要富文本处理）
     * @return 单元格值(若为富文本单元格，返回富文本html值)
     */
    public Object getCellValue(Row row, int column, RichExcelField ef) {
        Object val = "";
        try {
            Cell cell = row.getCell(column);
            if (cell != null) {
                if (hssEvaluator != null) {
                    HSSFCell hssfCell = (HSSFCell) cell;
                    if (hssfCell.getCellType() == CellType.FORMULA) {
                        CellValue tempCellValue = hssEvaluator.evaluate(hssfCell);
                        return tempCellValue.getNumberValue();
                    }
                } else {
                    XSSFCell xssfCell = (XSSFCell) cell;
                    if (xssfCell.getCellType() == CellType.FORMULA) {
                        CellValue tempCellValue = xssEvaluator.evaluate(xssfCell);
                        return tempCellValue.getNumberValue();
                    }
                }
                if (cell.getCellType() == CellType.NUMERIC) {
                    val = cell.getNumericCellValue();
                } else if (cell.getCellType() == CellType.STRING) {
                    if (ef!=null && ef.rich()) {
                        if (hssEvaluator != null) {
                            List<ImportFontStyle> importFontStyles = HSSFImport((HSSFWorkbook) this.wb, (HSSFCell) cell);
                            val = getHSSFRichText((HSSFCell) cell, importFontStyles);
                        } else {
                            List<ImportFontStyle> importFontStyles = XSSFImport((XSSFCell) cell);
                            val = getXSSFRichText((XSSFCell) cell, importFontStyles);
                        }
                    } else {
                        val = cell.getStringCellValue();
                    }
                } else if (cell.getCellType() == CellType.FORMULA) {
                    val = cell.getCellFormula();
                } else if (cell.getCellType() == CellType.BOOLEAN) {
                    val = cell.getBooleanCellValue();
                } else if (cell.getCellType() == CellType.ERROR) {
                    val = cell.getErrorCellValue();
                }
            }
        } catch (Exception e) {
            return val;
        }
        return val;
    }

    /**
     * Excel 07版本（.xlsx） 单元格内容转成富文本html
     * @param xssfCell  单元格内容
     * @param xssfCellStyles   单元格内容字体样式集合
     * @return
     */
    private static String getXSSFRichText(XSSFCell xssfCell, List<ImportFontStyle> xssfCellStyles) {
        XSSFRichTextString richStringCellValue = xssfCell.getRichStringCellValue();
        HashMap<Integer, String> sizeMap = RichFontStyle.getSizeMap();
        Map<String,String> currentHtmlMap = new LinkedHashMap<>();
        StringBuffer richText = new StringBuffer("<p></p>");
        Integer insertIndex = 3;
        for (int i=0; i < xssfCellStyles.size(); i++) {
            ImportFontStyle importFontStyle = xssfCellStyles.get(i);
            String currentFont = richStringCellValue.toString().substring(i, i + 1);
            Map<String, String> htmlMap = getHtmlMap(importFontStyle,sizeMap);
            List<String> removeHtmlList = currentHtmlMap.keySet().stream().filter(v -> !htmlMap.containsKey(v)).collect(Collectors.toList());
            if (removeHtmlList.size()>0){
                for (String item:removeHtmlList){
                    String html = currentHtmlMap.get(item);
                    richText.insert(insertIndex,html);
                    insertIndex = insertIndex + html.length();
                    currentHtmlMap.remove(item);
                }
            }
            List<String> addHtmlList = htmlMap.keySet().stream().filter(v -> !currentHtmlMap.containsKey(v)).collect(Collectors.toList());
            if (addHtmlList.size()>0){
                for (String item:addHtmlList){
                    String html = "<"+item+">";
                    richText.insert(insertIndex,html);
                    insertIndex = insertIndex + html.length();
                    currentHtmlMap.put(item,"</"+item+">");
                }
            }
            richText.insert(insertIndex,currentFont);
            insertIndex ++;
            if (i==xssfCellStyles.size()-1){
                for (String endHtmlKey:currentHtmlMap.keySet()){
                    String endHtml = currentHtmlMap.get(endHtmlKey);
                    richText.insert(insertIndex,endHtml);
                    insertIndex = insertIndex + endHtml.length();
                }
            }
        }
        return richText.toString();
    }

    /**
     * Excel 03版本（.xls） 单元格内容转成富文本html
     * @param hssfCell          单元格内容
     * @param hssfCellStyles    字体样式集合
     * @return      富文本html字符串
     */
    private static String getHSSFRichText(HSSFCell hssfCell, List<ImportFontStyle> hssfCellStyles) {
        HSSFRichTextString richStringCellValue = hssfCell.getRichStringCellValue();
        HashMap<Integer, String> sizeMap = RichFontStyle.getSizeMap();
        Map<String,String> currentHtmlMap = new LinkedHashMap<>();//当前存在的标签
        StringBuffer richText = new StringBuffer("<p></p>");
        Integer insertIndex = 3;
        for (int i=0; i < hssfCellStyles.size(); i++) {
            ImportFontStyle importFontStyle = hssfCellStyles.get(i);
            String currentFont = richStringCellValue.toString().substring(i, i + 1);
            Map<String, String> dueHtmlMap = getHtmlMap(importFontStyle,sizeMap);//当前字体应有标签集合
            Map<String, String> endHtmlMap = new LinkedHashMap<>();
            for (String html : currentHtmlMap.keySet()) {
                endHtmlMap.put(html,currentHtmlMap.get(html));
            }
            List<String> retainHtmlList = new ArrayList<>();
            for (String html : endHtmlMap.keySet()) {
                if (!dueHtmlMap.containsKey(html)) {
                    break;
                } else {
                    retainHtmlList.add(html);
                }
            }
            retainHtmlList.stream().forEach(v->{ endHtmlMap.remove(v); });

            //需要移除的标签（即需要添加结尾标签）
            List<String> removeHtmlList = endHtmlMap.keySet().stream().collect(Collectors.toList());
            if (removeHtmlList.size()>0){
                for (int n = removeHtmlList.size()-1 ; n >= 0 ; n-- ) {
                    String html = currentHtmlMap.get(removeHtmlList.get(n));
                    richText.insert(insertIndex,html);
                    insertIndex = insertIndex + html.length();
                    currentHtmlMap.remove(removeHtmlList.get(n));
                }
            }

            //需要新增的标签（应有标签 过滤掉 当前已存在的标签 --》 需要新增的标签）
            List<String> addHtmlList = dueHtmlMap.keySet().stream().filter(v -> !currentHtmlMap.containsKey(v)).collect(Collectors.toList());
            if (addHtmlList.size()>0){
                for (String item:addHtmlList){
                    String html = "<"+item+">";
                    richText.insert(insertIndex,html);
                    insertIndex = insertIndex + html.length();
                    currentHtmlMap.put(item, dueHtmlMap.get(item));
                }
            }
            richText.insert(insertIndex,currentFont);
            insertIndex ++;

            //遇到结尾，处理结束标签
            if (i==hssfCellStyles.size()-1){
                ListIterator<Map.Entry<String, String>> item = new ArrayList<>(currentHtmlMap.entrySet()).listIterator(currentHtmlMap.size());
                LinkedHashMap<String, String> linkedHashMap = new LinkedHashMap();
                while (item.hasPrevious()) {
                    Map.Entry<String, String> entry = item.previous();
                    linkedHashMap.put(entry.getKey(), entry.getValue());
                }
                for (String endHtmlKey:linkedHashMap.keySet()){
                    String endHtml = currentHtmlMap.get(endHtmlKey);
                    richText.insert(insertIndex,endHtml);
                    insertIndex = insertIndex + endHtml.length();
                }
            }
        }
        System.out.println("富文本："+richText.toString());
        return richText.toString();
    }

    /**
     * Excel 03版本（.xls） 单元格内容转成富文本html
     * @param workbook  工作流
     * @param cell      单元格内容
     * @return  返回每个字的字体样式集合
     */
    private static List<ImportFontStyle> HSSFImport(HSSFWorkbook workbook, HSSFCell cell) {
        HSSFRichTextString richStringCellValue = cell.getRichStringCellValue();
        List<ImportFontStyle> fontStyleList = new ArrayList<>();
        for (int i = 0; i < richStringCellValue.length(); i++) {
            ImportFontStyle importFontStyle = new ImportFontStyle();
            HSSFFont fontAt = workbook.getFontAt((int)richStringCellValue.getFontAtIndex(i));
            ImportFontStyle.Font font = new ImportFontStyle.Font();
            font.setFontName(fontAt.getFontName());
            HSSFColor hssfColor = fontAt.getHSSFColor(workbook);
            String hssfargbHex = PoiStyleUtil.getHSSFARGBHex(hssfColor);
            font.setFontColor(hssfargbHex);
            font.setFontSize(fontAt.getFontHeightInPoints());
            importFontStyle.setFont(font);
            importFontStyle.setFontBlod(fontAt.getBold());
            importFontStyle.setItalic(fontAt.getItalic());
            importFontStyle.setUnderline(fontAt.getUnderline()==1);
            importFontStyle.setStrikeout(fontAt.getStrikeout());
            fontStyleList.add(importFontStyle);
        }
        return fontStyleList;
    }

    /**
     * Excel 07版本（.xlsx） 单元格字符串样式获取
     * @param cell  单元格 cell
     * @return  返回单元格每个字的字体样式
     */
    private static List<ImportFontStyle> XSSFImport(XSSFCell cell) {
        XSSFRichTextString richStringCellValue = cell.getRichStringCellValue();
        List<ImportFontStyle> fontStyleList = new ArrayList<>();
        for (int i = 0; i < richStringCellValue.length(); i++) {
            ImportFontStyle importFontStyle = new ImportFontStyle();
            XSSFFont fontAtIndex = richStringCellValue.getFontAtIndex(i);
            ImportFontStyle.Font font = new ImportFontStyle.Font();
            font.setFontName(fontAtIndex.getFontName());
            XSSFColor xssfColor = fontAtIndex.getXSSFColor();
            font.setFontColor(PoiStyleUtil.getXSSFARGBHex(xssfColor));
            font.setFontSize(fontAtIndex.getFontHeightInPoints());
            importFontStyle.setFont(font);
            importFontStyle.setFontBlod(fontAtIndex.getBold());
            importFontStyle.setItalic(fontAtIndex.getItalic());
            importFontStyle.setUnderline(fontAtIndex.getUnderline()==1);
            importFontStyle.setStrikeout(fontAtIndex.getStrikeout());
            fontStyleList.add(importFontStyle);
        }
        return fontStyleList;
    }

    /**
     * 数字转字母 1-26 ： A-Z(适用于excel排序顺序)
     *
     * @param num 数字
     * @return 字母
     */
    public static String numberToLetter(int num) {
        if (num <= 0) {
            return null;
        }
        StringBuilder letter = new StringBuilder();
        num--;
        do {
            if (letter.length() > 0) {
                num--;
            }
            letter.insert(0, ((char) (num % 26 + (int) 'A')));
            num = (num - num % 26) / 26;
        } while (num > 0);

        return letter.toString();
    }

    /**
     * 根据字体样式实体，抽取html标签集合
     * @param importFontStyle   字体样式实体
     * @param sizeMap           字体大小样式转换集合
     * @return  返回字体应包含的标签集合
     */
    private static Map<String,String> getHtmlMap(ImportFontStyle importFontStyle,HashMap<Integer, String> sizeMap) {
        Map<String,String> htmlMap = new LinkedHashMap<>();
        if (importFontStyle.getFontBlod()) {
            htmlMap.put("b","</b>");
        }
        if (importFontStyle.getStrikeout()) {
            htmlMap.put("strike","</strike>");
        }
        if (importFontStyle.getUnderline()) {
            htmlMap.put("u","</u>");
        }
        if (importFontStyle.getItalic()) {
            htmlMap.put("i","</i>");
        }
        if (sizeMap.containsKey(importFontStyle.getFont().getFontSize())){
            htmlMap.put("font size='"+sizeMap.get(importFontStyle.getFont().getFontSize())+"'","</font>");
        }
        if (!StringUtils.equals(importFontStyle.getFont().getFontColor(),"#000000")){
            htmlMap.put("font color='"+importFontStyle.getFont().getFontColor()+"'","</font>");
        }
        return htmlMap;
    }

    /**
     * 获取合并单元格的值
     *
     * @param row    行
     * @param column 列
     * @return 单元格内容
     */
    public Object getMergedRegionValue(int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = this.sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return getCellValue(this.sheet.getRow(firstRow), firstColumn, null);
                }
            }
        }
        return null;
    }

    /**
     * 判断指定的单元格是否是合并单元格
     *
     * @param row
     * @param column
     * @return
     */
    public boolean isMergedRegion(int row, int column) {
        int sheetMergeCount = this.sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = this.sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return true;
                }
            }
        }
        return false;
    }

}
